package com.mbc.rmt.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.mbc.common.dao.DAO;
import com.mbc.common.param.CriteriaParams;
import com.mbc.rmt.data.ACGroupDetailDataList;
import com.mbc.rmt.entities.ACGroupDetail;

public class ACGroupDetailDAOImpls extends DAO<ACGroupDetail> implements
		ACGroupDetailDAO {

	public ACGroupDetailDAOImpls(Connection conn) {
		this.conn = conn;
		this.table_name = "ACGroupDetail";
	}

	@Override
	public void create(ACGroupDetail entity) throws SQLException {
		query = "insert into "
				+ table_name
				+ " ( acgroupdetailId, acgroupdetailCode, actype, acgroup, description, BranchId) values( ?, ?, ?, ?, ?, ?)";
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setLong(i++, entity.getAcgroupdetailId());
		pstmt.setString(i++, entity.getAcgroupdetailCode());
		pstmt.setInt(i++, entity.getActype());
		pstmt.setInt(i++, entity.getAcgroup());
		pstmt.setString(i++, entity.getDescription());
		pstmt.setLong(i++, entity.getBranchId());
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public void update(ACGroupDetail entity) throws SQLException {
		query = "update "
				+ table_name
				+ " set acgroupdetailCode = ?, actype = ?, acgroup = ?, description = ?, BranchId = ?"
				+ "where acgroupdetailId = " + entity.getAcgroupdetailId();
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setString(i++, entity.getAcgroupdetailCode());
		pstmt.setInt(i++, entity.getActype());
		pstmt.setInt(i++, entity.getAcgroup());
		pstmt.setString(i++, entity.getDescription());
		pstmt.setLong(i++, entity.getBranchId());
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public void delete(long key) throws SQLException {
		query = getDeleteQuery() + " where acgroupdetailId = " + key;
		pstmt = conn.prepareStatement(query);
		pstmt.executeUpdate();
		pstmt.close();

	}

	@Override
	public ACGroupDetail find(long key) throws SQLException {
		ACGroupDetail l_ACGroupDetail = null;
		query = getSelectQuery() + " where acgroupdetailId = " + key;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			l_ACGroupDetail = readRecord(rs);
		rs.close();
		pstmt.close();
		return l_ACGroupDetail;
	}

	@Override
	public ACGroupDetail findByCode(String code,long branchId) throws SQLException {
		ACGroupDetail l_ACGroupDetail = null;
		query = getSelectQuery() + " where acgroupdetailCode = '" + code + "' and BranchId = " + branchId;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			l_ACGroupDetail = readRecord(rs);
		rs.close();
		pstmt.close();
		return l_ACGroupDetail;
	}

	@Override
	public List<ACGroupDetail> findAll() throws SQLException {
		ArrayList<ACGroupDetail> l_ACGroupDetailList = new ArrayList<ACGroupDetail>();
		query = getSelectQuery();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		while (rs.next()) {
			l_ACGroupDetailList.add(readRecord(rs));
		}
		rs.close();
		pstmt.close();
		return l_ACGroupDetailList;
	}

	@Override
	public ACGroupDetailDataList findACGroupDetails(CriteriaParams cri)
			throws SQLException {
		ACGroupDetailDataList l_DataList = new ACGroupDetailDataList();
		ArrayList<ACGroupDetail> l_entityList = new ArrayList<ACGroupDetail>();
		String l_cri = " and (acgroupdetailCode like N'%" + cri.getKeyword()
				+ "%'" + " or description like N'%" + cri.getKeyword() + "%')" +
				" and BranchId = " + cri.getBranchId();
		query = "set rowcount "
				+ cri.getSize()
				+ " select * from (select row_number() over(order by acgroupdetailId) as [rowno], * from "
				+ table_name + " where 1 = 1" + l_cri + ") " + table_name
				+ " where rowno > " + cri.getIndex();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			do {
				l_entityList.add(readRecord(rs));
			} while (rs.next());
		}
		l_DataList.setEntityList(l_entityList);
		rs.close();
		pstmt.close();
		query = "select count(acgroupdetailId) from " + table_name
				+ " where 1 = 1" + l_cri;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			l_DataList.setTotalRecord(rs.getLong(1));
		}
		rs.close();
		pstmt.close();
		return l_DataList;
	}

	@Override
	protected ACGroupDetail readRecord(ResultSet rs) throws SQLException {
		ACGroupDetail l_entity = new ACGroupDetail();
		l_entity.setAcgroupdetailId(rs.getLong("acgroupdetailId"));
		l_entity.setAcgroupdetailCode(rs.getString("acgroupdetailCode"));
		l_entity.setActype(rs.getInt("actype"));
		l_entity.setAcgroup(rs.getInt("acgroup"));
		l_entity.setDescription(rs.getString("description"));
		l_entity.setBranchId(rs.getLong("BranchId"));
		return l_entity;
	}

	@Override
	public List<ACGroupDetail> getACGroupDetailList(ACGroupDetail info) throws SQLException {
		ArrayList<ACGroupDetail> l_ACGroupDetailList = new ArrayList<ACGroupDetail>();
		query = getSelectQuery() + " where actype = " + info.getActype() + " and acgroup =" + info.getAcgroup();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		while (rs.next()) {
			l_ACGroupDetailList.add(readRecord(rs));
		}
		rs.close();
		pstmt.close();
		return l_ACGroupDetailList;
	}

}
